/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');


// Schema 结构
var TgSpecialTopicModel = function(obj){
    ItbBaseModel.call(this,obj);

    // 以下为表定义数据，需要的字段就填入
    //`st_id` bigint(20) unsigned NOT NULL COMMENT '专题ID',
    //`title` varchar(50) NOT NULL DEFAULT '' COMMENT '专题名',
    //`desc` varchar(200) NOT NULL DEFAULT '' COMMENT '专题简介',
    //`thumb` text NOT NULL COMMENT '专题图示',
    //`order` int(3) NOT NULL DEFAULT '999' COMMENT '显示顺序',
    //`use` int(3) NOT NULL DEFAULT '0' COMMENT '用途: 1热门, 2专题',
    //`type` int(3) NOT NULL DEFAULT '0' COMMENT '分类: 0未知 1单页, 2多页, 3ppt'
    //`create_time` datetime NOT NULL,
    //`update_time` datetime NOT NULL,
    //`update_by` varchar(50) NOT NULL DEFAULT '',
    //`del_flg` int(1) NOT NULL DEFAULT '0'

};
TgSpecialTopicModel.prototype = new ItbBaseModel();

// 取得表名
TgSpecialTopicModel.prototype.getTableName = function(){
    return 'tg_special_topic';
}
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
TgSpecialTopicModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'st_id='+this.st_id;
}

// 关联tg_type表
TgSpecialTopicModel.prototype.findSpecTopicWithType = function(callback,skipCnt,pageSize){
    var THIS = this;
    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql = ' SELECT s.*, t.parent AS parent_type, t.top_parent AS top_type '+
            ' FROM tg_special_topic s, tg_type t '+
            ' WHERE s.del_flg=0 AND s.publish=1 AND t.del_flg=0 AND s.type=t.type_id ';
        if(Number(THIS.use||'0')>0){
            sql += ' AND s.use='+THIS.use;
        }
        if(Number(THIS.type||'0')>0){
            sql += ' AND s.type='+THIS.type;
        }

        if (THIS.order_by) {
            sql += ' ORDER BY ' + THIS.order_by;
        } else {
            sql += ' ORDER BY s.order, s.update_time DESC ';
        }

        if(typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
};

// 找到专题下面的项目列表
/**
 * 找到专题下面的项目列表
 *
 * @param callback 回调
 * @param skipCnt 忽略个数
 * @param pageSize 页容量
 * @param vipFlg 是否获取 vip 记录, 不传递或者 false 标识不获取 vip记录, true 标识获取 vip记录
 *
 * */
TgSpecialTopicModel.prototype.findRelItemsFromDB = function(callback,skipCnt,pageSize, vipFlg){
    var THIS = this;
    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            " SELECT distinct ret.*, "+
            " IFNULL(s.apply_cnt,0) as apply_cnt, IFNULL(s.show_apply_cnt,0) as show_apply_cnt, "+// added 2017-05-12
            " IFNULL(s.view_cnt,0) as view_cnt, IFNULL(s.show_view_cnt,0) as show_view_cnt "+// added 2017-05-12
            " FROM ( "+
            "   select x.*, func_parse_string_array(y.thumbs,0) AS goods_thumb, y.item_name AS goods_name, "+
            "     CASE WHEN x.goods_id IS NULL " +
            " OR x.goods_id <= 0" + // 增加判断, 商品 id 为0 也认为是非法商品 id, add by wangjun 2017-10-27
            " THEN '免费' ELSE CONCAT(TRUNCATE(y.price/100, 2),'元/',y.show ,y.unit) END AS goods_desc"+// added 2017-05-12
            "   from (select s.width,s.height,r.type_id, r.item_id AS tmpl_id, d1.design_name AS tmpl_name, p1.thumb "+
            "         ,r.order, d1.order AS tmpl_order "+
            "         ,d1.update_time, d1.create_time, d1.item_id AS goods_id, d1.vip "+ // add 2017-07-20 增加 vip 返回字段
            "   from `tg_special_topic_r` r,`tg_design_doc` d1,`tg_design_page` p1 , `tg_size` s "+
            "   where  r.del_flg=0 and r.`type_id` in (1,2,4,6,7,8) and r.st_id="+THIS.st_id+ // add 2017-07-20 增加支持分类 7,8
            "   and d1.design_id=r.item_id and d1.publish=1 and d1.del_flg=0 ";
            if(!vipFlg) {
                sql += " and d1.vip=0";  // add 2017-07-20 增加 vip 过滤
            }

            sql += "   and p1.design_id=r.item_id and p1.page_index=0 and p1.del_flg=0 and d1.size_id=s.size_id and s.del_flg = 0) x "+
            "   LEFT JOIN ( "+// modified 2017-05-12
            "     select i.item_id, i.item_name, i.thumbs, qp.price, qp.show, qp.unit "+
            "     from itb_items i, itb_print_rel pr, itb_print_param pp, itb_qty_price qp, "+
            "        (select qp2.pp_id, min(qp2.min) as valmin from itb_qty_price qp2 where qp2.del_flg=0 group by qp2.pp_id) qp3 "+
            "     where i.del_flg=0 and i.state=20 AND pr.del_flg=0 AND pr.item_id=i.item_id "+
            "       AND pp.del_flg=0 and pp.pp_id=pr.pp_id "+
            "       AND qp.del_flg=0 and qp.pp_id=pp.pp_id "+
            "       AND qp.pp_id=qp3.pp_id and qp.min=qp3.valmin "+
            "       GROUP BY i.item_id "+// 一个商品可能检索出对应多个套餐的最低价,group by用于取其中一个
            "     ) y on y.item_id=x.goods_id "+// 关联qp3取最小价格 added 2017-05-12
            "   UNION ALL "+
            "   (select 1600 as width,900 as height, r.type_id, r.item_id AS tmpl_id, d1.tmpl_name, p1.thumb_s as thumb "+
            "           ,r.order, d1.order AS tmpl_order "+
            "           ,d1.update_time, d1.create_time, null AS goods_id, d1.vip, null AS goods_thumb, null AS goods_name, '免费' AS goods_desc "+ // add 2017-07-20 增加 vip 返回字段
            "   from `tg_special_topic_r` r,`itb_template` d1,`itb_tmpl_page` p1 "+
            "   where r.del_flg=0 and r.`type_id`=3 and r.st_id="+THIS.st_id+
            "   and d1.tmpl_id=r.item_id and d1.publish=1 and d1.del_flg=0 ";
            if(!vipFlg) {
                sql += " and d1.vip=0";  // add 2017-07-20 增加 vip 过滤
            }

           sql += "   and p1.tmpl_id=r.item_id and p1.page_index=0 and p1.del_flg=0) "+
            " ) ret "+
            " LEFT JOIN itb_tmpl_stats s ON s.del_flg=0 and s.type=ret.type_id and s.tmpl_id=ret.tmpl_id "+// 关联统计表 added 2017-05-12
            " ORDER BY ret.order, ret.tmpl_order, ret.create_time DESC ";


        if(typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
};

// 找到热门下面的项目列表
// 参数 type: 1单页, 2多页, 3ppt, 40100(印品/名片), 40200(印品/传单) 60100(社交媒体/公众号封面大图) 60200(社交媒体/公众号封面小图) 60300(社交媒体/微博封面图)
/**
 * 获取首页热门系列下的项目列表
 *
 * @param callback 回调
 * @param type 分类 id, 热门中包含的才可使用
 * @param skipCnt 跳过记录个数
 * @param pageSize 请求页容量
 * @param vipFlg 是否获取包含 vip 的记录, 不传或 false 标识不获取vip记录, true 标识获取 vip 记录, 默认false, 不获取,
 * */
TgSpecialTopicModel.prototype.findHotRelItemsFromDB = function(callback,type,skipCnt,pageSize, vipFlg){
    var THIS = this;
    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql ='';
        if(type==3){
            // ppt
            sql = " SELECT x1.*, CASE WHEN x2.tmpl_price IS NULL THEN '免费' ELSE x2.tmpl_price END AS tmpl_price, "+
            " IFNULL(st.apply_cnt,0) as apply_cnt, IFNULL(st.show_apply_cnt,0) as show_apply_cnt, "+// added 2017-05-12
            " IFNULL(st.view_cnt,0) as view_cnt, IFNULL(st.show_view_cnt,0) as show_view_cnt "+// added 2017-05-12
            " FROM "+
            " (select 1600 as width,900 as height, r.item_id, st.order AS st_order, r.order AS st_r_order, d1.order AS tmpl_order, p1.thumb_s as thumb "+
            "  ,d1.update_time,d1.create_time, d1.tmpl_name" +
            "  ,d1.vip "+  //  add 2017-07-20 增加 vip 字段
            " from `tg_special_topic` st,`tg_special_topic_r` r,`itb_template` d1,`itb_tmpl_page` p1"+
            " where st.st_id=r.st_id and st.del_flg=0 and st.use=1 and st.`type`=3"+
            " AND st.publish=1 and r.del_flg=0 "+
            " and d1.tmpl_id=r.item_id and d1.publish=1 and d1.del_flg=0";
            if(!vipFlg) {
                sql += " and d1.vip=0 ";  // add 2017-07-20 增加 vip 过滤
            }
            sql +=" and p1.tmpl_id=r.item_id and p1.page_index=0 and p1.del_flg=0"+
            " ) x1 "+
            // ADD 2017-02-17
            " LEFT JOIN "+
            " (SELECT tp.tmpl_id, concat(TRUNCATE(MIN(tp.price)/100, 2),'元') AS tmpl_price "+
            " FROM itb_tmpl_price tp "+
            " WHERE tp.del_flg=0 AND tp.type=3 GROUP BY tp.tmpl_id) x2 "+
            " ON x2.tmpl_id = x1.item_id "+
            // 关联统计表 Added 2017-05-12
            " LEFT JOIN itb_tmpl_stats st ON st.del_flg=0 and st.type=3 and st.tmpl_id=x1.item_id "+
            " ORDER BY x1.st_order, x1.st_r_order, x1.tmpl_order, x1.create_time DESC ";

        } else if(type==1 || type==2 || type==60100 || type==60200 || type==60300 || type==70100 || type==70200 || type==8){
            // 单页, 多页, 社交媒体, 电商
            sql = " SELECT x1.*, CASE WHEN x2.tmpl_price IS NULL THEN '免费' ELSE x2.tmpl_price END AS tmpl_price, "+
            " IFNULL(st.apply_cnt,0) as apply_cnt, IFNULL(st.show_apply_cnt,0) as show_apply_cnt, "+// added 2017-05-12
            " IFNULL(st.view_cnt,0) as view_cnt, IFNULL(st.show_view_cnt,0) as show_view_cnt "+// added 2017-05-12
            " FROM "+
            " (select  s.width,s.height, r.item_id, st.order AS st_order, r.order AS st_r_order, d1.order AS tmpl_order, p1.thumb "+
            " ,d1.update_time,d1.create_time,d1.design_name AS tmpl_name " +
            " ,d1.vip "+ //  add 2017-07-20 增加 vip 字段
            " from `tg_special_topic` st,`tg_special_topic_r` r,`tg_design_doc` d1,`tg_design_page` p1, `tg_size` s "+
            " where st.st_id=r.st_id and st.del_flg=0 and st.use=1 and st.`type`="+type+
            " AND st.publish=1 and r.del_flg=0 "+
            " and d1.design_id=r.item_id and d1.publish=1 and d1.del_flg=0 ";
            if(!vipFlg) {
                sql += " and d1.vip=0 "; // add 2017-07-20 增加 vip 过滤
            }
            sql += " and p1.design_id=r.item_id and p1.page_index=0 and p1.del_flg=0 "+
            " and d1.size_id=s.size_id and s.del_flg = 0"+ // ADD 2017-04-12 增加返回尺寸
            " ) x1 "+
            // ADD 2017-02-17
            " LEFT JOIN "+
            " (SELECT tp.tmpl_id, MIN(tp.price), "+
            " concat(TRUNCATE(tp.price/100, 2),'元') AS tmpl_price "+
            " FROM itb_tmpl_price tp "+
            " WHERE tp.del_flg=0 AND tp.type IN (1,2,6,7,8) GROUP BY tp.tmpl_id) x2 "+ // add 2017-07-31 增加微商类型统计数据
            " ON x2.tmpl_id = x1.item_id "+
            // 关联统计表 Added 2017-05-12
            " LEFT JOIN itb_tmpl_stats st ON st.del_flg=0 and st.type IN (1,2,6,7,8) and st.tmpl_id=x1.item_id "+ // add 2017-07-31 增加微商类型统计数据
            " ORDER BY x1.st_order, x1.st_r_order, x1.tmpl_order, x1.create_time DESC ";

        } else if(type==40100 || type==40200 || type==40300 || type==40500 || type==40600 || type==40700 || type==40800) {
            // 印品/名片, 印品/传单, 印品/展架用图, 海报, 优惠券, 不干胶, 停车牌
            sql = 'SELECT x1.*, '+
                ' IFNULL(st.apply_cnt,0) as apply_cnt, IFNULL(st.show_apply_cnt,0) as show_apply_cnt, '+// added 2017-05-12
                ' IFNULL(st.view_cnt,0) as view_cnt, IFNULL(st.show_view_cnt,0) as show_view_cnt '+// added 2017-05-12
                ' FROM '+
                ' (SELECT s.width,s.height, t.design_id, t.item_id AS goods_id, func_parse_string_array(t.thumbs,0) AS thumb '+
                ' ,t.update_time, t.create_time ' +
                ' ,st.order AS st_order, r.order AS st_r_order, dd.order AS tmpl_order '+
                ' ,t.item_name AS goods_name, MIN(qp.min) ' +// ADD 2017-02-17
                ' ,concat("印刷 ",TRUNCATE(qp.price/100, 2),"元/",qp.show ,qp.unit) as goods_price ' +// ADD 2017-02-17
                ' ,dd.vip ' + // add 2017-07-20 增加 vip 标记
                ' FROM tg_special_topic st, tg_special_topic_r r, itb_items t ' +
                '      , itb_print_rel pr, itb_print_param pp, itb_qty_price qp, tg_design_doc dd , `tg_size` s ' +// ADD 2017-03-24
                '      , (select qp2.pp_id, min(qp2.min) as valmin from itb_qty_price qp2 where qp2.del_flg=0 group by qp2.pp_id) qp3 '+// added 2017-5-12
                ' WHERE st.del_flg=0 AND st.use=1 AND st.publish=1 AND st.type=' + type +
                ' AND r.del_flg=0 AND r.st_id=st.st_id ' +
                ' AND t.del_flg=0 AND t.state=20 AND r.item_id=t.design_id ' +
                ' AND pr.del_flg=0 AND pr.item_id=t.item_id ' +// ADD 2017-02-17
                ' AND pp.del_flg=0 AND pp.pp_id=pr.pp_id ' +// ADD 2017-02-17
                ' AND qp.del_flg=0 AND qp.pp_id=pp.pp_id ' +// ADD 2017-02-17
                ' AND qp.pp_id=qp3.pp_id and qp.min=qp3.valmin '+// 关联qp3取最小价格 added 2017-5-12
                ' and dd.size_id=s.size_id and s.del_flg = 0 ' + // ADD 2017-04-12 增加返回尺寸
                ' AND dd.item_id = t.item_id  AND dd.publish = 1  AND dd.del_flg=0 '; // add 2017-03-24 增加模板发布限制
                if(!vipFlg) {
                    sql += ' AND dd.vip=0 '; // add 2017-07-20 增加 vip 过滤
                }

            sql += ' GROUP BY t.item_id ' +// ADD 2017-02-17
                ' ) x1 '+
                    // 关联统计表 Added 2017-05-12
                ' LEFT JOIN itb_tmpl_stats st ON st.del_flg=0 and st.type=4 and (st.tmpl_id=x1.design_id OR st.item_id=x1.goods_id) '+
                ' ORDER BY x1.st_order, x1.st_r_order, x1.tmpl_order, x1.create_time DESC ';
        } else {
            connection.release();
            return callback('参数错误', null);
        }

        if(typeof skipCnt!='undefined' && skipCnt != null && typeof pageSize!='undefined' && pageSize != null){
            sql += " limit " + Number(skipCnt) + "," + Number(pageSize);
        }

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
};

//--------------------------------end-------------------------------

// 导出
module.exports = TgSpecialTopicModel;